package br.com.churrascariabrasadeouro.bd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.hsqldb.server.Server;

public class BancoDeDados {
	
    private String  serverProps;
    private String  url = "jdbc:hsqldb:hsql:.";
    private String  user     = "sa";
    private String  password = "";
    private final Server server = new Server();
	
	public BancoDeDados() {
		start();
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(this + ".ERRO: " + e.getMessage());
        }


	 }
	

	public void start() {
		server.start();
		criarBancoDeDados();
	}

	public void stop() {
		server.shutdown();
	}


     public  void criarBancoDeDados() {   
    	 Connection c  = null;
        try {
           c = obterConexao();
           Statement stmt = c.createStatement();
           stmt.execute("SELECT * FROM PRODUTO");
        } catch (SQLException e) {
        	try {
				c = obterConexao();
				Statement stmt = c.createStatement();
				stmt.addBatch("CREATE TABLE USUARIO (ID IDENTITY, NOME VARCHAR(250), USUARIO VARCHAR(10), SENHA VARCHAR(10), DATAHORA TIMESTAMP); ");
				stmt.addBatch("CREATE TABLE PRODUTO (ID IDENTITY, DESCRICAO VARCHAR(200), VALOR DOUBLE, DATAHORA TIMESTAMP); ");
				stmt.addBatch("CREATE TABLE COMANDA (ID IDENTITY, DATA TIMESTAMP, VALOR DOUBLE, DATAHORA TIMESTAMP); ");
				stmt.addBatch("CREATE TABLE ITEMCOMANDA (ID IDENTITY, COMANDAID INTEGER, PRODUTOID INTEGER, QUANTIDADE INTEGER, VALOR DOUBLE, DATAHORA TIMESTAMP, " +
						"FOREIGN KEY (COMANDAID) REFERENCES COMANDA (ID), " +
						"FOREIGN KEY (PRODUTOID) REFERENCES PRODUTO (ID)); ");
				stmt.executeBatch();
				e.printStackTrace();
			} catch (SQLException e1) {
				System.out.println("Não foi possivel criar as tabelas: "+e1.getMessage());
			}

		}
        
    }


    public Connection obterConexao() throws SQLException {
    	return DriverManager.getConnection(url, user, password);
    }


}
